package com.enation.app.javashop.core.wms.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.enation.app.javashop.core.goods.model.dos.CategoryDO;
import com.enation.app.javashop.core.goods.service.CategoryManager;
import com.enation.app.javashop.core.promotion.seckill.model.dos.SeckillApplyDO;
import com.enation.app.javashop.core.wms.model.dto.WmsOrderQueryParam;
import com.enation.app.javashop.core.wms.model.enums.LeaderDispatchTypeEnums;
import com.enation.app.javashop.core.wms.model.vo.*;
import com.enation.app.javashop.core.wms.service.WmsOrderExcelManager;
import com.enation.app.javashop.core.wms.utils.WmsSqlBuilder;
import com.enation.app.javashop.framework.context.ThreadContextHolder;
import com.enation.app.javashop.framework.database.DaoSupport;
import com.enation.app.javashop.framework.exception.ServiceException;
import com.enation.app.javashop.framework.util.BeanUtil;
import com.enation.app.javashop.framework.util.DateUtil;
import com.enation.app.javashop.framework.util.DictUtils;
import com.enation.app.javashop.framework.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

import static java.util.stream.Collectors.*;

/**
 * @author JFeng
 * @date 2020/9/23 18:01
 */
@Service
public class WmsOrderExcelManagerImpl implements WmsOrderExcelManager {

    @Autowired
    @Qualifier("tradeDaoSupport")
    private DaoSupport daoSupport;

    @Autowired
    private CategoryManager categoryManager;

    public static final String siteIdPre = "site_ids";

    /**
     * 订单sku
     * @param wmsOrderQueryParam
     */
    @Override
    public void exportSkuOrderList(WmsOrderQueryParam wmsOrderQueryParam) {
        StringBuilder sqlBuilder = new StringBuilder();

        wmsOrderQueryParam.setDeliveryType(LeaderDispatchTypeEnums.SELF_GLOBAL.name());
        List<Object> term = WmsSqlBuilder.skuOrderQuery(wmsOrderQueryParam, sqlBuilder);

        List<ExportSkuOrderVO> skuOrderVOList = daoSupport.queryForList(sqlBuilder.toString(),ExportSkuOrderVO.class,term.toArray());

        List<CategoryDO> firstClass=categoryManager.getFirstClass();
        Map<Integer, CategoryDO> firstClassMap = firstClass.stream().collect(toMap(CategoryDO::getCategoryId, Function.identity()));
        for (ExportSkuOrderVO exportSkuOrderVO : skuOrderVOList) {
            if (StringUtils.isEmpty(exportSkuOrderVO.getCategoryPath())) {
                continue;
            }
            Integer firstCategoryId = Integer.valueOf(exportSkuOrderVO.getCategoryPath().split("\\|")[1]);
            exportSkuOrderVO.setCategoryName(firstClassMap.get(firstCategoryId).getName());
        }

        HttpServletResponse response = getResponse("每日商品找格子");

        // 输出
        try {
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            String sheetName="每日商品找格子-("+wmsOrderQueryParam.getReceiveTimeName()+")";
            WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetName).head(ExportSkuOrderVO.class).build();
            excelWriter.write(skuOrderVOList,writeSheet);

            excelWriter.finish();
        } catch (Exception e) {
            throw new ServiceException("500", "导出失败");
        }
    }

    @Override
    public void exportSkuSupplierList(WmsOrderQueryParam wmsOrderQueryParam) {
        StringBuilder sqlBuilder = new StringBuilder();
        List<Object> termAll = WmsSqlBuilder.skuSupplierQuery(wmsOrderQueryParam, sqlBuilder,null);

        // 所有商品
        List<ExportSkuSupplyVO> listAll = daoSupport.queryForList(sqlBuilder.toString(),ExportSkuSupplyVO.class,termAll.toArray());
        // 岱山
        wmsOrderQueryParam.setIsDaiShan(true);
        sqlBuilder = new StringBuilder();
        List<Object> termDaishan = WmsSqlBuilder.skuSupplierQuery(wmsOrderQueryParam, sqlBuilder,null);
        List<ExportSkuSupplyVO> listDaishan = daoSupport.queryForList(sqlBuilder.toString(),ExportSkuSupplyVO.class,termDaishan.toArray());

        // 衢山超市
        wmsOrderQueryParam.setIsQuShan(true);
        wmsOrderQueryParam.setIsQushanMarket(true);
        wmsOrderQueryParam.setIsDaiShan(false);
        sqlBuilder = new StringBuilder();
        List<Object> termQushanMarket = WmsSqlBuilder.skuSupplierQuery(wmsOrderQueryParam, sqlBuilder,null);
        List<ExportSkuSupplyVO> listQushanMarket = daoSupport.queryForList(sqlBuilder.toString(),ExportSkuSupplyVO.class,termQushanMarket.toArray());

        // 衢山
        wmsOrderQueryParam.setIsQushanMarket(false);
        sqlBuilder = new StringBuilder();
        List<Object> termQushan = WmsSqlBuilder.skuSupplierQuery(wmsOrderQueryParam, sqlBuilder,null);
        List<ExportSkuSupplyVO> listQushan = daoSupport.queryForList(sqlBuilder.toString(),ExportSkuSupplyVO.class,termQushan.toArray());

        // 衢山汇总减去衢山超市
        List<ExportSkuSupplyVO> listQushanNoMarket = listQushan.stream()
                .filter(item -> !listQushanMarket.stream()
                        .map(e -> e.getSkuId())
                        .collect(Collectors.toList())
                        .contains(item.getSkuId()))
                .collect(Collectors.toList());

        //汇总表减去衢山超市
        List<ExportSkuSupplyVO> listAllCopy = new ArrayList<>();
        if (!CollectionUtils.isEmpty(listQushanMarket)) {
            Map<Integer, Integer> qushanMarketMap = listQushanMarket.stream().collect(Collectors.toMap(ExportSkuSupplyVO::getSkuId, ExportSkuSupplyVO::getGoodsCount));
            for (ExportSkuSupplyVO skuSupplyVO : listAll) {
                ExportSkuSupplyVO sku = new ExportSkuSupplyVO();
                BeanUtil.copyProperties(skuSupplyVO, sku);
                Integer qushanMarketGoodsCount = qushanMarketMap.get(sku.getSkuId());
                if (qushanMarketGoodsCount != null)
                    sku.setGoodsCount(sku.getGoodsCount() - qushanMarketGoodsCount);
                listAllCopy.add(sku);
            }
        }

        // 本岛
        wmsOrderQueryParam.setIsQuShan(false);
        wmsOrderQueryParam.setIsDaiShan(false);
        wmsOrderQueryParam.setIsBenDao(true);
        sqlBuilder = new StringBuilder();
        List<Object> termBenDao = WmsSqlBuilder.skuSupplierQuery(wmsOrderQueryParam, sqlBuilder,null);
        List<ExportSkuSupplyVO> listBenDao = daoSupport.queryForList(sqlBuilder.toString(),ExportSkuSupplyVO.class,termBenDao.toArray());

        // 已出库的sku汇总
        wmsOrderQueryParam.setIsQuShan(false);
        wmsOrderQueryParam.setIsDaiShan(false);
        wmsOrderQueryParam.setIsBenDao(false);
        sqlBuilder = new StringBuilder();
        List<Object> outOfStockSKU = WmsSqlBuilder.skuSupplierQuery(wmsOrderQueryParam, sqlBuilder,1);
        List<ExportSkuSupplyVO> listOutOfStockSKU = daoSupport.queryForList(sqlBuilder.toString(),ExportSkuSupplyVO.class,outOfStockSKU.toArray());

        List<CategoryDO> firstClass=categoryManager.getFirstClass();
        Map<Integer, CategoryDO> firstClassMap = firstClass.stream().collect(toMap(CategoryDO::getCategoryId, Function.identity()));

        // 获取秒杀商品的秒杀价
        List<Integer> skuIdList = listAll.stream().map(ExportSkuSupplyVO::getSkuId).collect(Collectors.toList());
        String sql = "SELECT a.* FROM es_seckill s LEFT JOIN es_seckill_apply a ON s.seckill_id=a.seckill_id\n" +
                "WHERE s.start_day=" + DateUtil.startOfTodDay() + " AND a.sku_id in (" + StringUtil.listToString(skuIdList, ",") + ")";
        List<SeckillApplyDO> applyList = this.daoSupport.queryForList(sql, SeckillApplyDO.class);
        Map<Integer, Double> applyMap = new HashMap<>();
        if (!CollectionUtils.isEmpty(applyList)) {
            applyMap = applyList.stream().collect(Collectors.toMap(SeckillApplyDO::getSkuId, SeckillApplyDO::getPrice, (oldKey, newKey) -> newKey));
        }

        // 所有商品
        composeGoodsCategory(listAll, firstClassMap,applyMap);
        // 本岛商品
        composeGoodsCategory(listBenDao, firstClassMap,applyMap);
        // 岱山商品
        composeGoodsCategory(listDaishan, firstClassMap,applyMap);
        // 衢山商品
        // composeGoodsCategory(listQushan, firstClassMap);
        composeGoodsCategory(listQushanNoMarket, firstClassMap,applyMap);

        // 衢山超市
        composeGoodsCategory(listQushanMarket, firstClassMap,applyMap);
        // 总表-衢山超市
        composeGoodsCategory(listAllCopy, firstClassMap,applyMap);

        // 已出库的sku汇总
        composeGoodsCategory(listOutOfStockSKU, firstClassMap,applyMap);


        HttpServletResponse response = getResponse("每日sku统计表");

        // 输出
        try {
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

            String sheet1="每日sku统计表"+wmsOrderQueryParam.getReceiveTimeName();
            WriteSheet writeSheet = EasyExcel.writerSheet(0, sheet1).head(ExportSkuSupplyVO.class).build();
            excelWriter.write(listAll, writeSheet);

            String sheet6="每日sku统计表_副表"+wmsOrderQueryParam.getReceiveTimeName();
            writeSheet = EasyExcel.writerSheet(1, sheet6).head(ExportSkuSupplyVO.class).build();
            excelWriter.write(listAllCopy, writeSheet);

            String sheet4="本岛SKU"+wmsOrderQueryParam.getReceiveTimeName();
            writeSheet = EasyExcel.writerSheet(2, sheet4).head(ExportSkuSupplyVO.class).build();
            excelWriter.write(listBenDao, writeSheet);

            String sheet2="岱山SKU(不计衢山镇)"+wmsOrderQueryParam.getReceiveTimeName();
            writeSheet = EasyExcel.writerSheet(3, sheet2).head(ExportSkuSupplyVO.class).build();
            excelWriter.write(listDaishan, writeSheet);

            String sheet3="衢山镇SKU"+wmsOrderQueryParam.getReceiveTimeName();
            writeSheet = EasyExcel.writerSheet(4, sheet3).head(ExportSkuSupplyVO.class).build();
            // excelWriter.write(listQushan, writeSheet);
            excelWriter.write(listQushanNoMarket, writeSheet);

            String sheet7="衢山超市供货表"+wmsOrderQueryParam.getReceiveTimeName();
            writeSheet = EasyExcel.writerSheet(5, sheet7).head(ExportSkuSupplyVO.class).build();
            excelWriter.write(listQushanMarket, writeSheet);

            String sheet5="已出库的sku汇总"+wmsOrderQueryParam.getReceiveTimeName();
            writeSheet = EasyExcel.writerSheet(6, sheet5).head(ExportSkuSupplyVO.class).build();
            excelWriter.write(listOutOfStockSKU, writeSheet);

            excelWriter.finish();
        } catch (IOException e) {
            throw new ServiceException("500", "导出失败");
        }
    }

    private void composeGoodsCategory(List<ExportSkuSupplyVO> listAll, Map<Integer, CategoryDO> firstClassMap,Map<Integer, Double> applyMap) {
        if (CollectionUtils.isEmpty(listAll)) {
            return;
        }

        for (ExportSkuSupplyVO exportSkuSupplyVO : listAll) {
            if (StringUtils.isEmpty(exportSkuSupplyVO.getCategoryPath())) {
                continue;
            }
            Integer firstCategoryId = Integer.valueOf(exportSkuSupplyVO.getCategoryPath().split("\\|")[1]);
            exportSkuSupplyVO.setCategoryName(firstClassMap.get(firstCategoryId).getName());

            // 如果是秒杀商品，价格改为秒杀价
            Double seckillPrice = applyMap.get(exportSkuSupplyVO.getSkuId());
            if (seckillPrice != null) {
                exportSkuSupplyVO.setPrice(seckillPrice);
            }
        }
    }

    @Override
    public void exportOrderSortingList(WmsOrderQueryParam wmsOrderQueryParam) {
        StringBuilder sqlBuilder = new StringBuilder();
        wmsOrderQueryParam.setDeliveryType(LeaderDispatchTypeEnums.SELF_GLOBAL.name());
        List<Object> term = WmsSqlBuilder.orderSortingQuery(wmsOrderQueryParam, sqlBuilder);
        List<ExportOrderSortingVO> list1 = daoSupport.queryForList(sqlBuilder.toString(),ExportOrderSortingVO.class,term.toArray());

        //  C模式  自提点联系方式
        StringBuilder sqlBuilder2 = new StringBuilder();
        List<Object> term2 = WmsSqlBuilder.orderDeliveryQuery(wmsOrderQueryParam, sqlBuilder2);
        List<ExportOrderDeliveryVO> list2 = daoSupport.queryForList(sqlBuilder2.toString(), ExportOrderDeliveryVO.class,term2.toArray());

        HttpServletResponse response = getResponse("每日订单找格子");

        // 输出
        try {
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            String sheetName="每日订单找格子-("+wmsOrderQueryParam.getReceiveTimeName()+")";
            WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).head(ExportOrderSortingVO.class).build();
            excelWriter.write(list1, writeSheet);

            sheetName="订单配送联系方式-("+wmsOrderQueryParam.getReceiveTimeName()+")";
            writeSheet = EasyExcel.writerSheet(1, sheetName).head(ExportOrderDeliveryVO.class).build();
            excelWriter.write(list2, writeSheet);

            excelWriter.finish();
        } catch (IOException e) {
            throw new ServiceException("500", "导出失败");
        }
    }

    @Override
    public void exportBModelList(WmsOrderQueryParam wmsOrderQueryParam) {
        wmsOrderQueryParam.setDeliveryType(LeaderDispatchTypeEnums.SELF_BATCH.name());

        // B模式 自提点商品配送汇总
        StringBuilder sqlBuilder1 = new StringBuilder();
        List<Object> term = WmsSqlBuilder.siteSkuQuery(wmsOrderQueryParam, sqlBuilder1);
        List<ExportSiteSkuVO> list1 = daoSupport.queryForList(sqlBuilder1.toString(), ExportSiteSkuVO.class, term.toArray());

        //  B模式  自提点联系方式
        StringBuilder sqlBuilder2 = new StringBuilder();
        List<Object> term2 = WmsSqlBuilder.siteListQuery(wmsOrderQueryParam, sqlBuilder2);
        List<ExportSiteVO> list2 = daoSupport.queryForList(sqlBuilder2.toString(), ExportSiteVO.class, term2.toArray());

        //  B模式  自提点客户联系表
        StringBuilder sqlBuilder3 = new StringBuilder();
        List<Object> term3 = WmsSqlBuilder.siteBuyerQuery(wmsOrderQueryParam, sqlBuilder3);
        List<ExportSiteBuyerVO> list3 = daoSupport.queryForList(sqlBuilder3.toString(), ExportSiteBuyerVO.class, term3.toArray());

        HttpServletResponse response = getResponse("B模式配送数据");

        // 输出
        try {
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            String receiveTimeName = wmsOrderQueryParam.getReceiveTimeName();

            // 获取导出表格的方式
            String type = this.choose();
            if (!StringUtil.isEmpty(type) && type.equals("split")) {
                excelWriter = this.splitExport(excelWriter, receiveTimeName, list1, list2, list3);
            } else {
                excelWriter = this.originalExport(excelWriter, receiveTimeName, list1, list2, list3);
            }

            excelWriter.finish();
        } catch (IOException e) {
            throw new ServiceException("500", "导出失败");
        }
    }


    private HttpServletResponse getResponse(String fileName) {
        HttpServletResponse response = ThreadContextHolder.getHttpResponse();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        return response;
    }

    //出库管理B端配送表合并导出（原始）
    private ExcelWriter originalExport(ExcelWriter excelWriter, String receiveTimeName, List<ExportSiteSkuVO> list1, List<ExportSiteVO> list2, List<ExportSiteBuyerVO> list3) {
        String sheetName = "自提点商品配送汇总-(" + receiveTimeName + ")";
        WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).head(ExportSiteSkuVO.class).build();
        excelWriter.write(list1, writeSheet);

        writeSheet = EasyExcel.writerSheet(1, "自提点联系方式").head(ExportSiteVO.class).build();
        excelWriter.write(list2, writeSheet);

        // sheetName = "自提点客户联系表-(" + receiveTimeName + ")";
        // writeSheet = EasyExcel.writerSheet(2, sheetName).head(ExportSiteBuyerVO.class).build();
        // excelWriter.write(list3, writeSheet);
        return excelWriter;
    }


    // 出库管理B端配送表分开导出
    private ExcelWriter splitExport(ExcelWriter excelWriter, String receiveTimeName, List<ExportSiteSkuVO> list1, List<ExportSiteVO> list2, List<ExportSiteBuyerVO> list3) {

        String sheetName = "自提点联系方式";
        WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).head(ExportSiteVO.class).build();
        excelWriter.write(list2, writeSheet);

        int sheetNum = 1;
        excelWriter = this.writeSiteMobile(list2, sheetNum++, excelWriter);

        sheetName = "自提点商品配送汇总-(" + receiveTimeName + ")";
        writeSheet = EasyExcel.writerSheet(sheetNum+=1, sheetName).head(ExportSiteSkuVO.class).build();
        excelWriter.write(list1, writeSheet);
        sheetNum++;
        if (!CollectionUtils.isEmpty(list1)) {
            //每个自提点一个sheet表
            Map<String, List<ExportSiteSkuVO>> map = list1.stream().filter(SiteSkuVO -> !StringUtil.isEmpty(SiteSkuVO.getSiteName()))
                    .collect(Collectors.groupingBy(ExportSiteSkuVO::getSiteName, Collectors.toList()));


            for (String key : map.keySet()) {
                List<ExportSiteSkuVO> list = map.get(key);
                writeSheet = EasyExcel.writerSheet(sheetNum++, key + sheetName).head(ExportSiteSkuVO.class).build();
                excelWriter.write(list, writeSheet);
            }

            //自提站点为空
            list1 = list1.stream().filter(SiteSkuVO -> StringUtil.isEmpty(SiteSkuVO.getSiteName())).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(list1)) {
                writeSheet = EasyExcel.writerSheet(sheetNum++, "非自提商品配送汇总").head(ExportSiteSkuVO.class).build();
                excelWriter.write(list1, writeSheet);
            }
        }

        if (!CollectionUtils.isEmpty(list3)) {
            writeSheet = EasyExcel.writerSheet(sheetNum++, "订单汇总表").head(ExportSiteBuyerVO.class).build();
            excelWriter.write(list3, writeSheet);

            List<ExportSiteBuyerVO> listQuShan = list3.stream().filter(siteBuyer -> siteBuyer.getSiteAddr().contains("衢山镇")).collect(toList());
            writeSheet = EasyExcel.writerSheet(sheetNum++, "衢山-订单汇总").head(ExportSiteBuyerVO.class).build();
            excelWriter.write(listQuShan, writeSheet);

            List<ExportSiteBuyerVO> listDaishan = list3.stream().filter(siteBuyer -> siteBuyer.getSiteAddr().contains("岱山")&& !siteBuyer.getSiteAddr().contains("衢山镇")).collect(toList());
            writeSheet = EasyExcel.writerSheet(sheetNum++, "岱山-订单汇总").head(ExportSiteBuyerVO.class).build();
            excelWriter.write(listDaishan, writeSheet);

            // Map<String, List<ExportSiteBuyerVO>> map = list3.stream().filter(siteBuyer -> !StringUtil.isEmpty(siteBuyer.getSiteName()))
            //         .collect(Collectors.groupingBy(ExportSiteBuyerVO::getSiteName, Collectors.toList()));
            // sheetName = "-自提点客户联系表-(" + receiveTimeName + ")";
            // for (String key : map.keySet()) {
            //     writeSheet = EasyExcel.writerSheet(sheetNum++, key + sheetName).head(ExportSiteBuyerVO.class).build();
            //     excelWriter.write(map.get(key), writeSheet);
            // }
        }
        return excelWriter;
    }


    // 岱山和衢山自提点联系方式单独放一张sheet
    private ExcelWriter writeSiteMobile(List<ExportSiteVO> list, int sheetNum, ExcelWriter excelWriter) {

        List<ExportSiteVO> qushanList=list.stream().filter(site->site.getCounty().equals("岱山县")&&site.getTown().equals("衢山镇")).collect(Collectors.toList());
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNum, "衢山自提点联系方式").head(ExportSiteVO.class).build();
        excelWriter.write(qushanList, writeSheet);

        List<ExportSiteVO> daishanList=list.stream().filter(site->site.getCounty().equals("岱山县")&&!site.getTown().equals("衢山镇")).collect(Collectors.toList());
        writeSheet = EasyExcel.writerSheet(sheetNum+=1, "岱山自提点联系方式").head(ExportSiteVO.class).build();
        excelWriter.write(daishanList, writeSheet);

        return excelWriter;
    }

    // 数据字典控制，出库管理B端配送表导出方式，"split"拆分导出，其它任何形式都是合并导出
    private String choose() {
        return DictUtils.getDictValue(null, "b_mode_export", "b_mode_export");
    }

}

